# -*- coding: utf8 -*-
'''
Created on 2011-5-2

@author: edison
'''
import MySQLdb
import sys
import datetime

class DBModel(object):
    def __init__(self,  user, passwd,database,host = "127.0.0.1", port = "3306"):
        self.host = host
        self.port = int(port)
        self.user = user
        self.passwd = passwd
        self.database = database;
        
    def setHost(self,host):
        self.host = host
            
    def setPort(self, port= "3306"):
        self.port = int(port)
    
    def opendb(self):
        try:
            self.conn = MySQLdb.connect(host=self.host, user=self.user, passwd=self.passwd, db=self.database, port=self.port, charset='utf8')
        except Exception, e:
            print 'database error:', e
            return
    
    def closedb(self):
        if self.__dict__.has_key('conn') : self.conn.close()
    
    def setTable(self,tablename):
        self.tablename = tablename
    
    def check_connect(self):
        if self.__dict__.has_key('conn'):
            self.cur = self.conn.cursor()
        else:
            self.opendb()
            self.cur = self.conn.cursor()
        
        
    def get_row(self,tablename = None):
        if tablename == None:
            pass
        else:
            self.setTable(tablename)
        
        self.check_connect()
        sql = '''select num_iid from %s where title is  NULL order by created_time''' % self.tablename
        try:
            self.cur.execute(sql)
            rows = self.cur.fetchall()
        finally:
            self.cur.close()
        return rows
    
	# def insertRow(self, tablename = None, data = {}):
	# 	if tablename == None:
	# 		return
	# 	else:
	# 		self.setTable(tablename)
	# 	self.check_connect()
	# 	
	# 	key = ''
	#         value = ''
	#         for k,v in dict.items():
	#             key = key + k + ','
	#             str = '\"%s\"' % v
	#             value = value + str + ','
	#         
	#         key = key[0:-1]
	#         value = value[0:-1]
	# 
	# 	SQl = "insert into %s ( %s ) VALUES ( %s )" % (self.tablename, key, value)
	# 	
	# 	try:
	#             self.cur.execute(SQL)
	#             self.conn.commit()
	#         except Exception, e:
	#             print >>sys.stderr, "[%s] MySQL Error: %s" % (datatime.datetime.today,e)
	#             self.conn.rollback()
	#         finally:
	#             self.cur.close()

    def replace_row(self, tablename = None, dict = {}):
        if tablename == None:
            pass
        else:
            self.setTable(tablename)
        self.check_connect()

       
        key = ''
        value = ''
        for k,v in dict.items():
            key = key + '`' + k + '`,'
            str = '\"%s\"' % v
            value = value + str + ','
        
        key = key[0:-1]
        value = value[0:-1]
        
        sql = "REPLACE INTO %s ( %s ) VALUES ( %s )" % (
                self.tablename,key,value)
        # print sql
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception, e:
            print >>sys.stderr, "[%s] MySQL Error: %s" % (datetime.datetime.today,e)
            self.conn.rollback()
        finally:
            self.cur.close()
        
    def update_row(self,tablename,num_iid, dict = {}):
        
        updateString = ""
        for k,v in dict.items():
            string = "%s = \"%s\"," % (k,v)
            updateString = updateString + string
        
        updateString = updateString[0:-1]

        self.check_connect()
        sql = "update %s SET %s where num_iid = \"%s\"" % (tablename, updateString, num_iid)
#        print sql
        try:
            self.cur.execute(sql)
            self.conn.commit()
        except Exception, e:
            print >>sys.stderr, "[%s] MySQL Error: %s" % (datetime.datetime.today, e)
            self.conn.rollback()
        finally:
            self.cur.close()
      
    def get_cid(self,tablename,tcid):
        self.check_connect()
        SQL = "select my_cid from %s where id = \'%s\'" % (tablename,tcid)
        rows = []
        try:
            self.cur.execute(SQL)
            rows = self.cur.fetchall()
        except Exception, e:
            print >>sys.stderr, "[%s] MySQL Error: %s" % (datetime.datetime.today, e)
#            self.conn.rollback()
        finally:
            self.cur.close
        return rows
            
if __name__ == '__main__':
    mysql = DBModel(host='10.0.1.254',user='qinzhoukan',passwd='qinzhoukan1@#',database='test')
#    mysql.opendb()
    mysql.setTable(tablename = "qin_item")
    print mysql.get_row()
    mysql.closedb()
    
    